<template>
  <div id="app">
    <div class="container">
      <div>
        <a
          href="http://blog.haoji.me/js-excel.html"
          _target="_blank"
        >如何使用JavaScript实现纯前端读取和导出excel文件</a>
        <br />
        <a href="http://oss.sheetjs.com/js-xlsx/">官网演示</a>
        <br />
        <a href="https://github.com/SheetJS/js-xlsx/">Github</a>
      </div>
      <h2>表头行号</h2>
      <input type="number" v-model="tableHeadRow" ref="tableHead" min="1" />
      <h2>读取excel（仅读取第一个sheet）</h2>
      <div class="mt-sm">
        <input
          type="file"
          id="file"
          accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;application/vnd.ms-excel"
        />
      </div>

      <p>结果输出：（下面表格可直接编辑导出）</p>
      <div id="result" contenteditable></div>

      <h2>导出excel</h2>
      <div class="mt-sm" style="padding-bottom:40px;">
        <input type="button" @click="exportExcel" value="保存" /> 上面读取的表格您可以直接编辑，编辑后点击保存即可导出excel文件。
      </div>
    </div>
  </div>
</template>

<script>
import xlsx from 'xlsx'
export default {
  name: 'App',
  data() {
    return {
      myBook: [],
      mySheet: [],
      originSheet: [],
      curSheet: null,
      mainSheet: {},
      mergeSheet: {},

      curColum: 'A',
      curRow: '1',
      maxColum: '',
      maxRow: '',

      tableHeadRow: 1,
      tableHeadContent: {},
    }
  },
  mounted() {
    this.init()
    window.toggleRow = this.toggleRow
    window.getRow = this.getRow
    window.getColum = this.getColum
  },
  methods: {
    init() {
      let self = this
      document.getElementById('file').addEventListener('change', function (e) {
        var files = e.target.files
        if (files.length == 0) return
        var f = files[0]
        if (!/\.xlsx$/g.test(f.name) && !/\.xls$/g.test(f.name)) {
          alert('仅支持读取xlsx和xls格式！')
          return
        }
        self.readWorkbookFromLocalFile(f, function (workbook) {
          self.readWorkbook(workbook)
        })
      })
    },

    selectFile() {
      document.getElementById('file').click()
    },

    // 读取本地excel文件
    readWorkbookFromLocalFile(file, callback) {
      let reader = new FileReader()
      reader.onload = (e) => {
        var data = e.target.result
        var workbook = xlsx.read(data, { type: 'binary' })
        this.myBook.push(workbook)
        if (callback) callback(workbook)
      }
      reader.readAsBinaryString(file)
    },

    // 读取表格数据
    readWorkbook(workbook) {
      let sheetNames = workbook.SheetNames // 工作表名称集合
      let worksheet = workbook.Sheets[sheetNames[0]] // 只读取第一张sheet
      this.curSheet = worksheet // 目前工作表
      this.tableHeadContent = this.getHead()
      let maxCR = this.getMaxColAndRow(worksheet) // 获取最大的列和行 [maxCol, maxRow]
      this.maxColum = maxCR[0]
      this.maxRow = maxCR[1]
      this.storeSheet(worksheet, this.tableHeadContent, maxCR) // 存入工作表栈
      // var csv = xlsx.utils.sheet_to_csv(worksheet);
      // document.getElementById('result').innerHTML = this.csv2table(csv);
    },
    // 存储工作表、表头、最大列和行
    storeSheet(sheet, tableHead, maxCR) {
      let obj = {
        sheet,
        tableHead,
        maxCR,
      }
      this.mySheet.push(obj)
      this.originSheet.push(Object.create(obj)) // 存一个原始副本
    },
    // 取最大行、列
    getMaxColAndRow(worksheet) {
      let maxCell = worksheet['!ref'].split(':')[1] // 右下角单元格位置
      let maxColum = this.getCellLetters(maxCell)[0]
      let maxRow = this.getCellNums(maxCell)[0]
      return [maxColum, maxRow]
    },

    // 获取表头信息
    getHead() {
      let arr = Object.keys(this.curSheet)
      let tableHeadContent = {}
      arr.forEach((cell) => {
        let num = this.getCellNums(cell)
        let letters = this.getCellLetters(cell)
        if (num === undefined || letters === undefined) {
          return
        }
        if (num == this.tableHeadRow) {
          tableHeadContent[letters] = this.curSheet[cell]
        }
      })
      return tableHeadContent
    },

    // 取数字
    getCellNums(cell) {
      let matchArr = cell.match(/[0-9]+/g)
      let result =
        typeof matchArr === 'object' && matchArr instanceof Array
          ? matchArr[0]
          : void 0
      return result
    },
    // 取字母
    getCellLetters(cell) {
      let matchArr = cell.match(/[A-Z]+/g)
      let result =
        typeof matchArr === 'object' && matchArr instanceof Array
          ? matchArr[0]
          : void 0
      return result
    },

    // 取特定行(无论某个单元格是否有数据),返回一个定位mySheet数据的数组
    getRow(targetRow, origin = this.mySheet[0]) {
      if (!origin || !origin.sheet || origin.sheet.length === 0) {
        alert('数据为空 - getRow')
        return
      }
      let result = []
      for (let i = 65; i < origin.maxCR[0].charCodeAt(); i++) {
        result.push(`${String.fromCharCode(i)}${targetRow}`)
      }
      return result
    },
    // 取特定列，同上
    getColum(targetColum, origin = this.mySheet[0]) {
      if (!origin || !origin.sheet || origin.sheet.length === 0) {
        alert('数据为空 - getColum')
        return
      }
      let result = []
      for (let i = 1; i < origin.maxCR[1]; i++) {
        result.push(`${targetColum}${i}`)
      }
      return result
    },

    // 交换行
    toggleRow(num1, num2, targetSheet = this.mySheet[0]) {
      if (
        !targetSheet ||
        !targetSheet.sheet ||
        !targetSheet.sheet.length === 0
      ) {
        alert('传入数据为空 - toggleRow')
        return
      }
      let row1 = this.getRow(num1, targetSheet)
      let row2 = this.getRow(num2, targetSheet)
      if (!row1 || !row2) {
        if (!row1 && !row2) {
          alert(`第${num1}行和第${num2}行无数据`)
          return
        } else if (!num1) {
          console.info(`第${num1}行无数据`)
        } else if (!num2) {
          console.info(`第${num2}行无数据`)
        }
      }
      let sheet = targetSheet.sheet
      row1.forEach((cell, i) => {
        let temp = sheet[row1[i]]
        sheet[row1[i]] = sheet[row2[i]]
        sheet[row2[i]] = temp
      })
    },

    table2csv(table) {
      var csv = []
      table.querySelectorAll('tr').forEach(function () {
        var temp = []
        table.querySelectorAll('td').forEach(function () {
          temp.push(table.innerHTML)
        })
        temp.shift() // 移除第一个
        csv.push(temp.join(','))
      })
      csv.shift()
      return csv.join('\n')
    },

    // csv转sheet对象
    csv2sheet(csv) {
      var sheet = {} // 将要生成的sheet
      csv = csv.split('\n')
      csv.forEach(function (row, i) {
        row = row.split(',')
        if (i == 0)
          sheet['!ref'] =
            'A1:' + String.fromCharCode(65 + row.length - 1) + (csv.length - 1)
        row.forEach(function (col, j) {
          sheet[String.fromCharCode(65 + j) + (i + 1)] = { v: col }
        })
      })
      return sheet
    },

    // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
    sheet2blob(sheet, sheetName) {
      sheetName = sheetName || 'sheet1'
      var workbook = {
        SheetNames: [sheetName],
        Sheets: {},
      }
      workbook.Sheets[sheetName] = sheet
      // 生成excel的配置项
      var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary',
      }
      var wbout = xlsx.write(workbook, wopts)
      var blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
      // 字符串转ArrayBuffer
      function s2ab(s) {
        var buf = new ArrayBuffer(s.length)
        var view = new Uint8Array(buf)
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
        return buf
      }
      return blob
    },

    /**
     * 通用的打开下载对话框方法，没有测试过具体兼容性
     * @param url 下载地址，也可以是一个blob对象，必选
     * @param saveName 保存文件名，可选
     */
    openDownloadDialog(url, saveName) {
      if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url) // 创建blob地址
      }
      var aLink = document.createElement('a')
      aLink.href = url
      aLink.download = saveName || '' // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      var event
      if (window.MouseEvent) event = new MouseEvent('click')
      else {
        event = document.createEvent('MouseEvents')
        event.initMouseEvent(
          'click',
          true,
          false,
          window,
          0,
          0,
          0,
          0,
          0,
          false,
          false,
          false,
          false,
          0,
          null
        )
      }
      aLink.dispatchEvent(event)
    },

    exportExcel() {
      // var csv = this.table2csv(document.querySelector('#result table'))
      // var sheet = this.csv2sheet(csv)
      var blob = this.sheet2blob(this.curSheet)
      this.openDownloadDialog(blob, '导出.xlsx')
    },
  },
}
</script>

<style>
table {
  border-collapse: collapse;
}

th,
td {
  border: solid 1px #6d6d6d;
  padding: 5px 10px;
}

.mt-sm {
  margin-top: 8px;
}

body {
  background: #f4f4f4;
  padding: 0;
  margin: 0;
}

.container {
  /* width: 1024px; */
  margin: 0 auto;
  background: #fff;
  padding: 20px;
  min-height: 100vh;
}
</style>
